﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace OL.FrameCore.Infrastructure.Document
{
    public class NPOIExcel
    {
        //创建工作薄
        protected HSSFWorkbook book;
        protected List<ISheet> sheetList;

        protected int rowIndex = 0;
        protected int sheetIndex = -1;

        /// <summary>
        /// 数据源
        /// </summary>
        //public DataTable DataSource { get; set; }
        private string TitleName { get; set; }
        private IList<object> _dataSource;

        private List<string> _titles;

        public NPOIExcel()
        {
            book = new HSSFWorkbook();
            sheetList = new List<ISheet>();
        }
        public NPOIExcel(string titleName, IEnumerable<object> dataSource)
        {
            book = new HSSFWorkbook();
            sheetList = new List<ISheet>();
            this.TitleName = titleName;
            _dataSource = dataSource.ToList();
            _titles = new List<string>();
        }

        private void CreateSheet()
        {
            sheetIndex++;


            ISheet sheet = null;
            if (string.IsNullOrEmpty(TitleName))
            {
                TitleName = "Sheet";
            }

            string title = TitleName + string.Format("({0})", sheetIndex + 1);
            sheet = book.CreateSheet(title);

            sheet.DisplayGridlines = false;
            sheet.DefaultColumnWidth = 20;
            sheetList.Add(sheet);
        }

        private void CreateHeader()
        {
            //创建表头行
            IRow headerRow = sheetList[sheetIndex].CreateRow(rowIndex);
            headerRow.HeightInPoints = 20;//行高

            ICellStyle style = book.CreateCellStyle();
            //水平对齐
            style.Alignment = HorizontalAlignment.Center;
            //背景色
            style.FillPattern = FillPattern.SolidForeground;
            //style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Tan.Index;//浅褐色
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;

            //字体
            //IFont font = book.CreateFont();
            //font.Color = NPOI.HSSF.Util.HSSFColor.Green.Index;
            //style.SetFont(font);
            //for (int i = 0; i < DataSource.Columns.Count; i++)
            //{
            //    ICell cell = headerRow.CreateCell(i);  //创建单元格
            //    cell.SetCellValue(DataSource.Columns[i].ColumnName);//往单元格中添加数据
            //    cell.CellStyle = style;
            //    //sheetList[sheetIndex].SetColumnWidth(i, StyleList[i].Width * 256);//列宽
            //}

            var type = _dataSource[0].GetType();
            var listPropertie = type.GetProperties();
            for (int i = 0; i < listPropertie.Length; i++)
            {
                ICell cell = headerRow.CreateCell(i);  //创建单元格
                cell.SetCellValue(listPropertie[i].Name);//往单元格中添加数据
                cell.CellStyle = style;
                _titles.Add(listPropertie[i].Name);
            }


            rowIndex++;

            //冻结
            sheetList[sheetIndex].CreateFreezePane(0, rowIndex, 0, rowIndex);
        }

        private void CreateData()
        {
            //内容样式列表
            List<ICellStyle> contentStyleList = new List<ICellStyle>();
            ////小计样式列表
            //List<ICellStyle> minTotalStyleList = new List<ICellStyle>();
            ////合计样式列表
            //List<ICellStyle> totalStyleList = new List<ICellStyle>();
            for (int j = 0; j < _titles.Count; j++)
            {
                //普通样式
                ICellStyle style = book.CreateCellStyle();
                SetBorder(style);
                //SetHorizontalAlignment(j, style);
                //SetDataFormat(j, style);
                IFont font = book.CreateFont();
                font.FontHeightInPoints = 9;
                style.SetFont(font);
                style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
                contentStyleList.Add(style);
            }

            //表内数据.不能超过65535
            for (int i = 0; i < _dataSource.Count(); i++)
            {
                //超过65535自动创建新表
                if (rowIndex == 65536)
                {
                    rowIndex = 0;
                    CreateSheet();
                    CreateHeader();
                }
                IRow row = sheetList[sheetIndex].CreateRow(rowIndex);
                var listPropertie = _dataSource[i].GetType().GetProperties();
                for (int j = 0; j < listPropertie.Length; j++)
                {
                    ICell cell = row.CreateCell(j);  //创建单元格
                    //string name = StyleList[j].DataField;

                    var value = listPropertie[j].GetValue(_dataSource[i]);
                    SetValue(cell, listPropertie[j].GetType(), value);
                    cell.CellStyle = contentStyleList[j];//内容样式
                }

                rowIndex++;
            }
        }


        public void Create(string filePath)
        {
            CreateSheet();
            CreateHeader();
            CreateData();

            using (FileStream fs = File.OpenWrite(filePath)) //打开一个xls文件，如果没有则自行创建
            {
                book.Write(fs);   //向打开的这个xls文件中写入mySheet表并保存。
            }
        }

        public MemoryStream Create()
        {
            CreateSheet();
            CreateHeader();
            CreateData();

            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            return ms;
        }

        /// <summary>
        /// 设置边框样式
        /// </summary>
        /// <param name="style"></param>
        protected void SetBorder(ICellStyle style)
        {
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
        }

        /// <summary>
        /// 设置值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="column"></param>
        /// <param name="value"></param>
        protected void SetValue(ICell cell, Type type, object value)
        {
            string strValue = value.ToString();
            switch (type.ToString())
            {
                case "System.String"://字符串类型
                    cell.SetCellValue(strValue);
                    break;
                case "System.DateTime"://日期类型
                    DateTime dateV;
                    if (DateTime.TryParse(strValue, out dateV))
                    {
                        cell.SetCellValue(dateV.ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        cell.SetCellValue("");
                    }

                    //cell.CellStyle = dateStyle;//格式化显示
                    break;
                case "System.Boolean"://布尔型
                    bool boolV = false;
                    bool.TryParse(strValue, out boolV);
                    cell.SetCellValue(boolV);
                    break;
                case "System.Int16"://整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(strValue, out intV);
                    cell.SetCellValue(intV);
                    break;
                case "System.Decimal"://浮点型
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(strValue, out doubV);
                    cell.SetCellValue(doubV);
                    break;
                case "System.DBNull"://空值处理
                    cell.SetCellValue("");
                    break;
                default:
                    cell.SetCellValue("");
                    break;
            }
        }
    }
}
